<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Master extends MX_Controller {


	function __construct()
	{
		parent::__construct();
		$this->load->model('dump/master_model');
	}
	
	function update_key(){
		$sql = "SELECT * FROM  `rawdata`";
		$result = $this->db->query($sql)->result();
		
		foreach ($result as $master){
			$key='';
			for($i=1; $i < 21; $i++){
				$f = "key$i";
				
				if($master->$f != ''){
					$key = trim($master->$f);
					
					
					$cat = $this->_getSpcl($key,0)->id;
					if($cat){
					
						$data = array(
							'listing_id'	=>	$master->id,
								'term'	=> 'key',
								'term_value'	=>	$cat,
								'caption'	=>'keyword'
								);

						
						$this->db->insert('term',$data);
					}
				}
				
				
			}
			
			
			
		}
	}
	
	
	function insert(){
		$sql = "SELECT * FROM  `rawdata`";
		$result = $this->db->query($sql)->result();
		
		foreach ($result as $master){
			
			$desc = "<b>$master->name</b> has special legal expertise in the Area of <b>$master->key1</b>.
				$master->name is practicing $master->key1 for long time in <b>$master->address1 $master->city, $master->state</b>.";
			
			$loc = $this->_getCity2($master->city);
			
			//print_r($loc);
			//die();
			$post = array(
					'id' => $master->id,
					'title' => $master->name,
					'address1' => $master->address1,
					'address2' => $master->address2,
					'city' => $loc[0]->id,
					'state' => $loc[0]->stateid,
					'country' => 2,
					'zip' => $master->Postcode,
					'phone1' => $master->Phone,
					'fax' => $master->Fax,
					'lat'	=> $master->Latitude,
					'long'	=> $master->Longitude,
					'email' => strtolower($master->email),
					'website' => strtolower($master->website),
					'description' => $desc,
					'status'	=> 51,
					
			);
				
			//$this->db->insert('post',$post);
			
			//die();
		}
		
		
			
	}
	
	function _getCity2($strCity){
		$query = $this->db->get_where('city', array('cityname' => $strCity,'countryid' =>2),1);
		if($query){
			return $query->result();
		}else{
			return null;
		}
	}
	
	
	function create_keyword(){
		$sql = "SELECT * FROM  `rawdata`";
		$result = $this->db->query($sql)->result();
		
		$config = array('table'=>'category_tree');
		$this->load->library('mahana_hierarchy');
		$this->mahana_hierarchy->initialize($config);
		
		foreach ($result as $master){
			
			$key = trim($master->key22);
			
			$sql = "select name from category_tree where name = '$key'";
			$result = $this->db->query($sql)->result();
			
			//print $sql;
			//die();
			
			if(!$result){
				$data = array(
					'name'=>	$key,
						'meta_title'	=> 'Find '.$key.' lawyer',
						'meta_keyword'	=> 'Find Lawyer, Find '.$key.' lawyer',
						'meta_description' => 'Find '.$key.' lawyer',
						'url'	=> $this->commoncall->url($key.'-'.'lawyer'),
						);
				
				//$this->mahana_hierarchy->insert($data);
			}
			
			//die($key);
		}
	}
	
	
	function create_city(){
		$sql = "SELECT city,state FROM  `rawdata` group by city";
		$result = $this->db->query($sql)->result();

		foreach ($result as $master){
				
			$state = $this->_getState($country=2,$state= trim($master->state));
				
			if($state){
				$data = array(
						'countryid'	=>$country,
						'stateid'	=>	$state,
						'cityname'	=> trim($master->city),
				);
				//$this->db->insert('city',$data);
				die();
			}
		}
	}

	function _getState($country,$state){
		$sql = "select id from state where countryid=$country and statename ="."'".$state."' limit 1";
		$query = $this->db->query($sql);
		return $query->row()->id;

	}

	function create_state(){
		$sql = "SELECT state FROM  `rawdata` group by state";
		$result = $this->db->query($sql)->result();

		$country=2;

		foreach ($result as $master){
			$data = array(
					'countryid'	=>$country,
					'statename'	=>	$master->state,
					'statecode'	=> 'NULL',
			);
				
			//$this->db->insert('state',$data);
			die();
		}
	}


	function ucfirst(){
		$sql = "SELECT state FROM  `rawdata` group by state";
		$result = $this->db->query($sql)->result();
		print count($result);

		foreach ($result as $master){
			//$city = ucwords(strtolower($master->city));
			$state = ucwords(strtolower($master->state));

			$updt = "update rawdata set state = '$state' where state = '$master->state'";
			//print $updt;
			$dd=$this->db->query($updt);

			//print $city;
			//die();
		}

	}

	function term(){
		$sql = "SELECT * FROM  `business_master` WHERE  `status` =51";
		$result = $this->db->query($sql)->result();

		$col = $this->db->list_fields('business_master');
		foreach ($result as $master){

			for($i=5; $i< count($col);$i++){
				$fld = $col[$i];
				if($master->$fld !=''){
					$data = array(
							'listing_id'	=> 	$master->id,
							'term'			=>  $fld,
							'term_value'	=>  $master->$fld,

					);
					//$this->db->insert('term',$data);
				}
			}
		}

	}


	function keyword(){

		$sql = "SELECT * FROM  listing_keyword";
		$result = $this->db->query($sql)->result();

		foreach ($result as $master){

			$sql = "INSERT INTO term (listing_id, term, term_value)
			VALUES ($master->listing_id, 'key', $master->key_id)";
			//---$this->db->query($sql);


		}
	}



	function check_city_doc(){
		$sql = "select id, city, state from dump group by city";
		$result = $this->db->query($sql)->result();
		$i=0;
		foreach ($result as $master){
			$sql = "select cityname,statecode from city where cityname='$master->city' and statecode='$master->state'";
			$result = $this->db->query($sql)->result();
			if(count($result)== 0){
				print $i++."-------".$master->id."--".$master->city."--".$master->state. "</br>";

				//$sql= "insert into city (countryid,stateid,statecode,cityname) VALUES
				//(1,0,'$master->state','$master->city')";
				//$this->db->query($sql);
			}
		}
	}


	function create_speciality(){

		$sql = "select speciality from dump group by speciality";
		$result = $this->db->query($sql)->result();

		$config = array('table'=>'category_tree');
		$this->load->library('mahana_hierarchy');
		$this->mahana_hierarchy->initialize($config);
			
			
		foreach ($result as $master){
			$new_comment['name'] = $master->speciality;
			$new_comment['parent_id'] = 8999206;
			//---$this->mahana_hierarchy->insert($new_comment);

		}

	}

	function _getSpcl($strSpcl='',$pID=0){
		$query = $this->db->get_where('category_tree', array('name' => $strSpcl,'parent_id' =>$pID),1);
		if($query){
			return $query->row();
		}else{
			return null;
		}
	}

	function insert_doc($limit=50000, $start=0){
		$msg = "Processing ........";
		print $msg;
		$sql = "select * from dump where status=0 group by full_name limit $start,$limit";
		$result = $this->db->query($sql)->result();
		foreach ($result as $master){
			//--Insert into Post --//
			$loc = $this->_getCity($master->city,$master->state);
			$cat = $this->_getSpcl($master->speciality,8999206);

			if($loc && $cat){
					
				$parent = 8999205;
				$child = $cat[0]->parent_id;
				$spec = $cat[0]->id;
					
					
					
					
				if($master->gender == 'Female') $gndr ='She';
				if($master->gender == 'Male') $gndr ='He';
					
				$desc = "<b>$master->name</b> is board certified in <b>$master->speciality</b> and $gndr has special expertise in the Area of $master->speciality.
				$master->name graduated from <b>$master->education</b>. $gndr is practicing since $master->since as a $master->speciality and practices in <b>$master->address1 $master->city, $master->state</b>.";

				//die($desc);
					
				$post = array(
						'id' => $master->id,
						'title' => $master->name,
						'address1' => $master->address1,
						'address2' => $master->address2,
						'city' => $loc[0]->id,
						'state' => $loc[0]->stateid,
						'country' => 1,
						'zip' => $master->zip,
						'phone1' => $master->phone,
						'fax' => $master->fax,
						'email' => strtolower($master->email),
						'website' => $master->website,
						'description' => $desc,
						'parent'	=>	$parent,
						'child'		=>	$child,
				);
					
				$this->db->insert('post',$post);
					
				//--Insert into Term --//
				if($master->gender) $this->_insert_term($post_id = $master->id, $term='gender', $term_value = $master->gender, $caption = 'Gender');
				if($master->education) $this->_insert_term($post_id = $master->id, $term='education', $term_value = $master->education, $caption = 'Education');
					
				if($master->residency) $this->_insert_term($post_id = $master->id, $term='residency', $term_value = $master->residency, $caption = 'Residency');
				if($master->s1) $this->_insert_term($post_id = $master->id, $term='speciality', $term_value = $master->s1.", ".$master->s2, $caption = 'Speciality');
				if($master->since) $this->_insert_term($post_id = $master->id, $term='since', $term_value = $master->since, $caption = 'Practicing Since');
				if($master->activity) $this->_insert_term($post_id = $master->id, $term='activity', $term_value = $master->activity, $caption = 'Activity');
				if($master->hospital) $this->_insert_term($post_id = $master->id, $term='hospital', $term_value = $master->hospital, $caption = 'Hospital');
				if($master->clinic) $this->_insert_term($post_id = $master->id, $term='clinic', $term_value = $master->clinic, $caption = 'Clinic');
				if($master->unit) $this->_insert_term($post_id = $master->id, $term='unit', $term_value = $master->unit, $caption = 'Unit');
					
				if($parent) $this->_insert_term($post_id = $master->id, $term='key', $term_value = $parent, $caption = 'Category');
				if($child) $this->_insert_term($post_id = $master->id, $term='key', $term_value = $child, $caption = 'Category');
				if($spec) $this->_insert_term($post_id = $master->id, $term='key', $term_value = $spec, $caption = 'Category');
					
					
				$this->db->query("update dump set status=1 where id = $master->id");
			}
		}

		$msg = "Complete .....";
		print $msg;
	}

	function _getCity($strCity,$strState){
		$query = $this->db->get_where('city', array('cityname' => $strCity,'statecode' =>$strState),1);
		if($query){
			return $query->result();
		}else{
			return null;
		}
	}

	function _insert_term($post_id = 0, $term='NULL', $term_value = 'NULL', $caption = 'NULL'){
		$post = array(
				'listing_id' => $post_id,
				'term' => $term,
				'term_value' => $term_value,
				'caption' => $caption
		);
		$this->db->insert('term',$post);
	}

	function insert_parent($parent){
		$new_comment['name'] = $parent;
		//---$insert_id = $this->mahana_hierarchy->insert($new_comment);
	}

	function insert_child($pID=0,$child="NEW CATEGORY"){
		$config = array('table'=>'category_tree');
		$this->load->library('mahana_hierarchy');
		$this->mahana_hierarchy->initialize($config);

		$new_comment['name'] = $child;
		$new_comment['parent_id'] = $pID;
		//---$this->mahana_hierarchy->insert($new_comment);
	}

	function getCity_byID($cityID=0,$stateID=0){
		$sql = "select cityname from city where id=$cityID and stateid=$stateID limit 1";
		$query = $this->db->query($sql);
		return $query->row()->cityname;
	}

	function getLatLong(){

		$sql = "SELECT *
		FROM  `post`
		WHERE  `lat` =  ''
		AND  `long` =  ''
		LIMIT 0 , 1000";

		$result = $this->db->query($sql)->result();
		foreach ($result as $master){
			$address = $master->address1.",".$master->address2."+".$this->getCity_byID($master->city,$master->state)."+United States";
			$url = "http://maps.google.com/maps/api/geocode/json?address=".$address."&sensor=false";
			$geocode = str_replace(" ","%20",$url);
			$geocode=file_get_contents($geocode);

			$output= json_decode($geocode);
			$lat = $output->results[0]->geometry->location->lat;
			$long = $output->results[0]->geometry->location->lng;

			if($long && $lat){
				$data = array(
						'lat'	=> $lat,
						'long'	=> $long,
				);
			}else{
				$data = array(
						'lat'	=> 0,
						'long'	=> 0,
				);
			}
			$this->db->where('id', $master->id);
			$this->db->update('post', $data);

			print $lat."---".$long ."</br>";

		}

	}

}